package com.schx.budget.mapper

import com.schx.budget.pojo.Budget
import com.schx.budget.pojo.BudgetTargetUpdate
import com.schx.budget.pojo.BudgetTarget
import com.schx.budget.pojo.BudgetType
import org.apache.ibatis.annotations.*

@Mapper
interface BudgetTargetMapper {

    @Select("<script>SELECT\n" +
            "    c.*,\n" +
            "    d.je month\n" +
            "FROM\n" +
            "    (\n" +
            "        SELECT\n" +
            "            content,\n" +
            "            id,\n" +
            "            amount_goal,\n" +
            "            item,\n" +
            "            orgid,\n" +
            "            create_date,\n" +
            "            update_date,\n" +
            "            type,\n" +
            "            year,\n" +
            "            month1,\n" +
            "            month2,\n" +
            "            month3,\n" +
            "            month4,\n" +
            "            month5,\n" +
            "            month6,\n" +
            "            month7,\n" +
            "            month8,\n" +
            "            month9,\n" +
            "            month10,\n" +
            "            month12,\n" +
            "            month11,\n" +
            "            year2020,\n" +
            "            0 goal\n" +
            "        FROM\n" +
            "            budget_target t\n" +
            "        WHERE\n" +
            "            t.type LIKE concat('%', concat(#{type},'%'))" +
            "        <if test='orgid != \"\"'> and t.orgid = #{orgid}  </if>" +
            "        <if test='orgid == \"\"'> and t.orgid = '1001'  </if>\n" +
            "    ) c,\n" +
            "    (\n" +
            "        SELECT\n" +
            "            b.content,\n" +
            "            SUM(g.amount) je\n" +
            "        FROM\n" +
            "            gross_monomer   g,\n" +
            "            budget_type                  a,\n" +
            "            budget_target                b\n" +
            "        WHERE\n" +
            "            g.bus_type3 = a.id " +
            "            <if test='orgid != \"\"'> and g.segment1 = #{orgid} and b.orgid = #{orgid}  </if>\n" +
            "            <if test='orgid == \"\"'> and b.orgid = '1001'</if>" +
            "            AND a.type1 = b.content\n" +
            "            AND to_char(g.done_date,'yyyy-mm') = #{month} \n" +
            "        GROUP BY\n" +
            "            b.content\n" +
            "    ) d\n" +
            "WHERE\n" +
            "    c.content = d.content\n" +
            "    order by month desc</script>")
    fun getTargetList(type: String, month: String, orgid: String): List<BudgetTarget>

    /**
     * 毛利年度目标
     */
    @Select("<script>SELECT\n" +
            "    c.*,\n" +
            "    d.je year2020\n" +
            "FROM\n" +
            "    (\n" +
            "        SELECT\n" +
            "            content,\n" +
            "            id,\n" +
            "            month,\n" +
            "            amount_goal,\n" +
            "            item,\n" +
            "            orgid,\n" +
            "            create_date,\n" +
            "            update_date,\n" +
            "            type,\n" +
            "            year,\n" +
            "            month1,\n" +
            "            month2,\n" +
            "            month3,\n" +
            "            month4,\n" +
            "            month5,\n" +
            "            month6,\n" +
            "            month7,\n" +
            "            month8,\n" +
            "            month9,\n" +
            "            month10,\n" +
            "            month12,\n" +
            "            month11,\n" +
            "            0 goal\n" +
            "        FROM\n" +
            "            budget_target t\n" +
            "        WHERE\n" +
            "            t.type LIKE concat('%', concat(#{type},'%')) <if test='orgid == \"\"'> and orgid = '1001' </if> <if test='orgid != \"\"'> and t.orgid = #{orgid}  </if> \n" +
            "    ) c,\n" +
            "          (<if test='orgid != \"\"'> " +
            "               SELECT\n" +
            "                   content,\n" +
            "                   je\n" +
            "               FROM\n" +
            "                   gross_monomer_2020\n" +
            "               where segment1 = #{orgid}" +
            "            </if>\n" +
            "            <if test='orgid == \"\"'> " +
            "               SELECT\n" +
            "                         content,\n" +
            "                         SUM(je) je\n" +
            "                     FROM\n" +
            "                         gross_monomer_2020\n" +
            "                     GROUP BY\n" +
            "                         content" +
            "            </if> " +
            "    ) d\n" +
            "WHERE\n" +
            "    c.content = d.content    " +
            "    ORDER by d.je desc" +
            "</script>")
    fun getInTargetList(type: String, lastYear: String, orgid: String): List<BudgetTarget>

    /**
     * 费用年度目标
     */
    @Select("<script>SELECT\n" +
            "    *\n" +
            "FROM\n" +
            "    budget_target\n" +
            "WHERE\n" +
            "    type LIKE concat('%', concat(#{type},'%')) " +
            "   <if test='orgid == \"\"'> and orgid = '1001' </if>" +
            "   <if test='orgid != \"\"'> and orgid = #{orgid} </if>\n" +
            "ORDER BY\n" +
            "    amount_goal DESC\n" +
            "    </script>")
    fun getCostTargetList(type: String, orgid: String): List<BudgetTarget>

    /**
     * 目标数保存
     */
    @Insert("<script>insert into budget_target" +
            "         (id,month,amount_goal,item,content,orgid,create_date,update_date,type,year,\n" +
            "month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12,year2020)" +
            "           values" +
            "(seq_budget_target.NEXTVAL,#{month},#{amount_goal},#{item},#{content},#{orgid}," +
            "#{create_date}," +
            "#{update_date},#{type},#{year},\n" +
            "#{month1},#{month2},#{month3},#{month4},#{month5},#{month6},#{month7}," +
            "#{month8},#{month9},#{month10},#{month11},#{month12},#{year2020})</script>")
    fun save(budgetTarget: BudgetTarget): Int

    /**
     * 更新目标目标金额
     */
    @Update("<script>update budget_target" +
            "   set amount_goal = #{amount_goal}," +
            "       update_date = #{update_date}," +
            "       month1      = #{month1}," +
            "       month2      = #{month2}," +
            "       month3      = #{month3}," +
            "       month4      = #{month4}," +
            "       month5      = #{month5}," +
            "       month6      = #{month6}," +
            "       month7      = #{month7}," +
            "       month8      = #{month8}," +
            "       month9      = #{month9}," +
            "       month10     = #{month10}," +
            "       month11     = #{month11}," +
            "       month12     = #{month12}," +
            "       year2020    = #{year2020}" +
            "   where id = #{id}</script>")
    fun update(budgetTarget: BudgetTarget): Int

    @Select("insert into budget_target_update(id,budget_id,amount_goal,type,orgid,month1,month2,month3,month4,month5,\n" +
            "month6,month7,month8,month9,month10,month11,month12,update_date,update_person)\n" +
            "values(#{id},#{budget_id},#{amount_goal},#{type},#{orgid},#{month1},#{month2},\n" +
            "#{month3},#{month4},#{month5},#{month6},#{month7},#{month8},#{month9},#{month10},\n" +
            "#{month11},#{month12},#{update_date},#{update_person})")
    fun insert(targetUpdate: BudgetTargetUpdate): Integer

    @Select("INSERT INTO budget_type (\n" +
            "        id,\n" +
            "        type1,\n" +
            "        type2,\n" +
            "        type3\n" +
            "    ) VALUES (\n" +
            "        #{id},\n" +
            "        #{type1},\n" +
            "        #{type2},\n" +
            "        #{type3}\n" +
            "    )")
    fun insertType(budgetType: BudgetType): Integer

    @Select("<script>SELECT\n" +
            "    COUNT(*)\n" +
            "FROM\n" +
            "    budget_type     t,\n" +
            "    budget_target   b\n" +
            "WHERE\n" +
            "    t.type1 = #{project}\n" +
            "    OR b.content = #{project}\n" +
            "    OR t.id = #{id}" +
            "    <if test='orgid != \"\"'> and orgid = #{orgid}  </if>" +
            "    <if test='orgid == \"\"'> and orgid = '1001'</if>" +
            "</script>")
    fun checkContent(project: String, id: String, orgid: String): Int

    @Select("<script>select content, amount_goal from budget_target " +
            "where type like '4%'  " +
            "<if test='orgid != \"\"'> and orgid = #{orgid}  </if>" +
            "<if test='orgid == \"\"'> and orgid = '1001'</if>" +
            "</script>")
    fun getProfits(orgid: String): List<BudgetTarget>

    /**
     * 获取毛利目标、费用目标
     */
    @Select("<script>SELECT\n" +
            "    SUM(amount_goal) budgetAmount,\n" +
            "    CASE\n" +
            "        WHEN substr(type, 0, 1) = '1' THEN\n" +
            "            '毛利'\n" +
            "        ELSE\n" +
            "            '费用'\n" +
            "    END project\n" +
            "FROM\n" +
            "    budget_target\n" +
            "    <if test='orgid != \"\"'> where orgid = #{orgid}   </if>\n" +
            "    <if test='orgid == \"\"'> WHERE orgid = '1001'</if>" +
            "GROUP BY\n" +
            "    substr(type, 0, 1)</script>")
    fun getSumProfits(orgid: String): List<Budget>

    /**
     * 获取政策性业务目标和
     */
    @Select("<script>SELECT\n" +
            "    SUM(amount_goal)\n" +
            "FROM\n" +
            "    budget_target   b,\n" +
            "    budget_type     t\n" +
            "WHERE\n" +
            "    t.type1 = b.content\n" +
            "    <if test='orgid != \"\"'> AND b.orgid = #{orgid}  </if>\n" +
            "    <if test='orgid == \"\"'> AND b.orgid = '1001'</if>" +
            "    AND t.type2 = '政策性业务'</script>")
    fun getSumPolicy(orgid: String): String

    @Select("<script>SELECT\n" +
            "    amount_goal\n" +
            "FROM\n" +
            "    budget_target\n" +
            "WHERE\n" +
            "    <if test='orgid != \"\"'> orgid = #{orgid} and  </if>\n" +
            "    <if test='orgid == \"\"'> orgid = '1001' AND</if>" +
            "    content LIKE '工资%'</script>")
    fun getWage(orgid: String): List<Int>

    @Select("<script>select usercount from budget_account <if test='orgid != \"\"'>where org_id = #{orgid}  </if></script>")
    fun getPersonNum(orgid: String): List<Int>

    @Delete("<script>delete from budget_target where id = #{id} <if test='orgid != \"\"'> and orgid = #{orgid}  </if></script>")
    fun delTarget(orgid: String, id: String): Int
}
